﻿using System;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;
using System.Collections.Generic;
using Home.Mobilesales.Common;
using Home.MobileSales.Common.Entities;


namespace Home.MobileSales.Common.Models
{
    public class OrderRepository
    {
        private List<Order>loadOrders (SqlCeCommand command)
        {
            using (SqlCeDataReader dr = command.ExecuteReader())
            {
                List<Order> orders = new List<Order>(5);
                Order order;
                int idOrder = dr.GetOrdinal("IdOrder");
                int dateOrder = dr.GetOrdinal("DateOrder");
                int sumOrder = dr.GetOrdinal("SumOrder");
                int numberOrder = dr.GetOrdinal("NumberOrder");
                int visitId = dr.GetOrdinal("VisitId");
                int skladId = dr.GetOrdinal("SkladId");
                int state = dr.GetOrdinal("State");
                while (dr.Read())
                {
                    order = new Order();
                    order.IdOrder = dr.GetInt32(idOrder);
                    order.DateOrder = dr.GetDateTime(dateOrder);
                    order.SumOrder = dr.GetDouble(sumOrder);
                    order.NumberOrder = dr.GetString(numberOrder);
                    order.VisitId = dr.GetInt32(visitId);
                    order.RecordOrders =
                        DataManager.GetInstance().OrderRecords.GetOrderRecordByOrder(order.IdOrder);
                    order.SumOrder = order.RecordOrders.Sum(r => r.Amount);
                    order.SkladId = dr.GetInt32(skladId);
                    char typeD = dr.GetValue(state).ToString()[0];
                    switch (typeD)
                    {
                        case 'E':
                            {
                                order.IsReadOnly = true;
                                break;
                            }
                        case 'R':
                            {
                                order.IsReadOnly = false;
                                break;
                            }
                        case'V':
                            {
                                order.IsReadOnly = true;
                                order.NotDelivery = true;
                                break;
                            }
                    }
                    orders.Add(order);

                }
                return orders;
            }
        }

        public List<Order> GetOrdersByVisit(int idVisit, bool isExport)
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();

                string sqlQuery = "SELECT IdOrder, DateOrder, SumOrder, NumberOrder, VisitId, SkladId, State" +
                                  " FROM Orders" +
                                  " Where VisitId = @VisitId";
                if (isExport)
                {
                    sqlQuery += " and State not in ('V')";
                }
                using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
                {
                    com.Parameters.Add("@VisitId", idVisit);
                    return loadOrders(com);
                }
            }
        }

        //public List<Order> GetExportOrderByVisit(int idVisit)
        //{
        //    using (SqlCeConnection connection = WorkDataBase.CreateConnection())
        //    {
        //        if (connection.State != ConnectionState.Open)
        //            connection.Open();

        //        string sqlQuery = "SELECT IdOrder, DateOrder, SumOrder, NumberOrder, VisitId, SkladId, State" +
        //                          " FROM Orders" +
        //                          " Where VisitId = @VisitId and State not in ('V') ";
        //        using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
        //        {
        //            com.Parameters.Add("@VisitId", idVisit);
        //            return loadOrders(com);
        //        }
        //    }
            
        //}

        public Order GetOrderById (int orderId)
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();

                string sqlQuery = "SELECT IdOrder, DateOrder, SumOrder, NumberOrder, VisitId, SkladId, State" +
                                  " FROM Orders" +
                                  " Where IdOrder = @IdOrder";
                using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
                {
                    com.Parameters.Add("@IdOrder", orderId);
                    return loadOrders(com).First();
                    
                }
            }
        }

        public Order CreateOrder(Order order)
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery =
                    "insert into Orders (VisitId, DateOrder, NumberOrder, SumOrder, SkladId, State) "+
                    "values (@VisitId, @DateOrder, @NumberOrder, @SumOrder, @SkladId, @State)";
                using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
                {
                    com.Parameters.Add("@VisitId", order.VisitId);
                    com.Parameters.Add("@DateOrder", order.DateOrder);
                    com.Parameters.Add("@NumberOrder",order.NumberOrder);
                    com.Parameters.Add("@SumOrder", SqlDbType.Float).Value = 0.00f;
                    com.Parameters.Add("@SkladId", order.SkladId);
                    com.Parameters.Add("@State", order.NotDelivery ? "V" : "R");
                    if (com.ExecuteNonQuery() < 1)
                    {
                        throw new OtherException("Не добавлено жодного запису");
                    }
                }
            }
            List<Order> orders = GetOrdersByVisit(order.VisitId, false);
            return orders.Single(o => o.IdOrder == (orders.Max(or => or.IdOrder)));
        }

        public void DeleteOrder (int idOrder)
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery = "delete from Orders where IdOrder = @IdOrder";
                using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
                {
                    com.Parameters.Add("@IdOrder", idOrder);
                    if (com.ExecuteNonQuery() < 1)
                    {
                        throw new OtherException("Не видалено жодного запису");
                    }
                }
            }
        }
    
        public void UpdateOrder (Order order)
        {
            using (SqlCeConnection connection = WorkDataBase.CreateConnection())
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                string sqlQuery =
                    "update orders set "+
                        "NumberOrder = @NumberOrder, DateOrder = @DateOrder, SumOrder = @SumOrder, SkladId = @SkladId, State = @State " +
                        "where idOrder = @IdOrder";
                using (SqlCeCommand com = new SqlCeCommand(sqlQuery, connection))
                {
                    com.Parameters.Add("@IdOrder", order.IdOrder);
                    com.Parameters.Add("@NumberOrder", order.NumberOrder);
                    com.Parameters.Add("@DateOrder", order.DateOrder);
                    com.Parameters.Add("@SumOrder", order.SumOrder);
                    com.Parameters.Add("@SkladId", order.SkladId);
                    char state = '0';
                    if (order.NotDelivery)
                        state = 'V';
                    else if (order.IsReadOnly)
                        state = 'E';
                    else
                        state = 'R';
                    com.Parameters.Add("@State", state.ToString());
                    if (com.ExecuteNonQuery() < 1)
                    {
                        throw new OtherException("Не змінено жодного запису");
                    }
                }
            }
        }

    }
    
}
